package com.ita.os.merchant.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.ita.os.merchant.dao.MerchantDao;
import com.ita.os.merchant.po.Audit;
import com.ita.os.merchant.po.Merchant;
import com.ita.os.merchant.util.DBUtil;

public class MerchantDaoImpl implements MerchantDao {
	
	@Override
	public int addMerchant(Merchant merchant) {
		Connection con = null;
		PreparedStatement pst = null;
		int count = 0;
		try {
			con = DBUtil.getConnection();
			con.setAutoCommit(false);
			String sql = "insert into merchant values(my_seq.nextval,?,?,?,?,?,?,?,?)";
			pst = con.prepareStatement(sql);
			pst.setString(1, merchant.getMname());
			pst.setString(2, merchant.getType());
			pst.setString(3, merchant.getAddress());
			pst.setString(4, merchant.getTel());
			pst.setString(5, merchant.getLogo());
			pst.setString(6, merchant.getInfo());
			pst.setString(7, merchant.getPassword());
			pst.setString(8, merchant.getEmail());
			count = pst.executeUpdate();
			con.commit();
/*			Merchant returnM = findMerchant(merchant.getTel(),merchant.getPassword());
			if(returnM != null)
				addMaudit(returnM.getMid());*/
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return count;
	}
	
	public int addMaudit(Integer mid){
		Connection con = null;
		PreparedStatement pst = null;
		int count = 0;
		try {
			con = DBUtil.getConnection();
			con.setAutoCommit(false);
			String sql = "insert into maudit(mid,status,aid) values(?,0,my_seq.nextval)";
			pst = con.prepareStatement(sql);
			pst.setInt(1, mid);
			count = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return count;
	}
	
	@Override
	public Merchant findMerchant(String tel,String password) {
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Merchant m = null;
		Audit audit = null;
		try {
			con = DBUtil.getConnection();
			String sql = "select * from merchant m left join maudit a on m.mid = a.mid where m.tel=? and m.password = ?";
			pst = con.prepareStatement(sql);
			pst.setString(1, tel);
			pst.setString(2, password);
			rs = pst.executeQuery();
			while (rs.next()) {
				m = new Merchant();
				m.setMid(rs.getInt("mid"));
				m.setMname(rs.getString("mname"));
				m.setType(rs.getString("type"));
				m.setAddress(rs.getString("address"));
				m.setTel(rs.getString("tel"));
				m.setLogo(rs.getString("logo"));
				m.setInfo(rs.getString("info"));
				m.setPassword(rs.getString("password"));
				m.setEmail(rs.getString("email"));
				audit = new Audit();
				audit.setAid(rs.getInt("aid"));
				audit.setStatus(rs.getInt("status"));
				m.setAudit(audit);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, rs);
		}
		return m;
	}

	@Override
	public int updateMerchant(Merchant merchant) {
		Connection con = null;
		PreparedStatement pst = null;
		int rs = 0;
		try {
			con = DBUtil.getConnection();
			con.setAutoCommit(false);
			String sql = "update merchant set mname = ? ,type = ?,address = ?,tel = ?,logo = ?,info = ?,password = ? where mid = ?";
			pst = con.prepareStatement(sql);
			pst.setString(1, merchant.getMname());
			pst.setString(2, merchant.getType());
			pst.setString(3, merchant.getAddress());
			pst.setString(4, merchant.getTel());
			pst.setString(5, merchant.getLogo());
			pst.setString(6, merchant.getInfo());
			pst.setString(7, merchant.getPassword());
			pst.setInt(8, merchant.getMid());
			
			rs = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, null);
		}
		return rs;
	}

	@Override
	public Merchant findByName(String mname) {
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		Merchant m = null;
		Audit audit = null;
		try {
			con = DBUtil.getConnection();
			String sql = "select * from merchant m left join maudit a on m.mid = a.mid where m.mname=? ";
			pst = con.prepareStatement(sql);
			pst.setString(1, mname);
			rs = pst.executeQuery();
			while (rs.next()) {
				m = new Merchant();
				m.setMid(rs.getInt("mid"));
				m.setMname(rs.getString("mname"));
				m.setType(rs.getString("type"));
				m.setAddress(rs.getString("address"));
				m.setTel(rs.getString("tel"));
				m.setLogo(rs.getString("logo"));
				m.setInfo(rs.getString("info"));
				m.setPassword(rs.getString("password"));
				audit = new Audit();
				audit.setAid(rs.getInt("aid"));
				audit.setStatus(rs.getInt("status"));
				m.setAudit(audit);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pst, rs);
		}
		return m;
	}

}
